﻿using System;
using System.Xml;
using UwaySoft.EsheetServer.BizLogic;
using UwaySoft.EsheetServer.DataLayer;
using UwaySoft.EsheetServer.Help.Response;
using UwaySoft.EsheetServer.Help.Template;
using System.Data;
using System.Configuration;

namespace UwaySoft.EsheetServer.Help.Template
{
    public class NewSheetAutoModel
    {
        protected ISqlHelper SqlHelper
        {
            get { return Application.SqlHelper; }
        }

        /// <summary>
        /// 获得投诉工单号
        /// </summary>
        /// <param name="nType">工单号类型</param>
        /// <returns>工单号</returns>
        public string AutoBulidID(int nType)
        {
            string num = SqlHelper.ExecuteScalar<decimal>("select seq_mod_task.nextval from dual").ToString();
            string strText = string.Format("XT{0}{1}", DateTime.Now.ToString("yyyy"), num);
            return strText;
        }

        public void AutoSendTask(NewSheetModel entity, NewSheetResponse itemres)
        {
            try
            {
                if (entity != null && itemres != null)
                {
                    UserCityHelper userhelp = new UserCityHelper();
                    string servicecontent = entity.Servicecontent;//服务内容
                    string groupsql = string.Format("SELECT * FROM cfg_complaints_auto where cityid={0}", userhelp.GetCityId(entity.Usercity));
                    DataSet ds = SqlHelper.ExecuteDateSet(groupsql);
                    if (ds != null && ds.Tables.Count > 0)
                    {
                        DataTable grouptable = ds.Tables[0];
                        if (grouptable != null && grouptable.Rows.Count > 0)
                        {
                            for (int i = 0; i < grouptable.Rows.Count; i++)
                            {
                                string groupname = grouptable.Rows[i]["groupname"].ToString();//组名
                                string groupman = grouptable.Rows[i]["groupman"].ToString();//接口人
                                string timeoutnum = grouptable.Rows[i]["timeoutnum"].ToString();//判断投诉类型时间
                                string issms = grouptable.Rows[i]["issms"].ToString();//是否短信

                                if (servicecontent.ToLower().IndexOf(groupname.ToLower()) >= 0)//包含关键字符
                                {
                                    BuildTask(entity, itemres, userhelp, groupman, timeoutnum, issms);
                                    break;
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception exp)
            {
                Log.Debug("自动派发工单失败，改为手动派发，原单号：" + itemres.EsheetNo);
                Log.Error(exp);
            }
        }

        public void BuildTask(NewSheetModel entity, NewSheetResponse itemres, UserCityHelper userhelp, string username, string timeout, string issms)
        {
            string taskid = AutoBulidID(52);//工单号
            string userphone = "";//作业计划执行人的联系电话
            string jobexecman = GetJobExecMan(userhelp.GetCityId(entity.Usercity).ToString(), ref userphone);//作业计划执行人（工单创建人）
            string timeoutlength = "";//超时时长

            //插入工单步骤
            string stepsql = string.Format(@"INSERT INTO MOD_TASK_STEP (ID,TASK_ID,STARTID,ENDID,EXECTIME,EXECMAN,NEXTMAN,TASKSTEPSTATE,TMPSTEPID,ISNOSTEPT,ISREAD)  
            VALUES ( SEQ_TASKSTEP.NEXTVAL,'{0}',1,2,to_Date('{1}','YYYY-MM-DD HH24:MI:SS'),'{2}','{3}',1,30309,'1',0)", taskid, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), jobexecman, username);
            SqlHelper.ExecuteNonQuery(stepsql);

            //查询工单步骤ID
            string stepidsql = string.Format(@"SELECT ID FROM Mod_Task_step WHERE task_id='{0}'", taskid);
            string stepid = SqlHelper.ExecuteScalar<decimal>(stepidsql).ToString();

            //插入工单
            string phone = "";
            if (!string.IsNullOrEmpty(entity.Acceptphone))
            {
                phone = entity.Acceptphone;
            }
            else
            {
                phone = entity.Userphone1;
            }
            string tasksql = string.Format(@"INSERT INTO MOD_TASK ( TASK_ID,TEMPLATE_ID,CREATEUSER,TEMPLATESTEPID,TASKSTEPID,TASKSTATE,COMPLAINCODE,COMPLAINSUBCODE,CREATDATE,TASK_TOPIC,COPYUSER,SQLVALUE)
 VALUES ('{0}',52,'{1}',30309,{2},3,'','',sysdate,'{3}','','SELECT complaints_id 入库编号 ,SHEETID  工单ID,EXPLAIN  业务类型,USERTYPE  品牌类别,USERMEAL  未定义,USERSIGN  客户品牌,EMAIL  E_MAI,USERREMARK  用户备注 ,USERCITY  用户归属地 ,CALLNO  主叫号码 ,ACCEPTPHONE  受理号码 ,USERPHONE1  联系电话1 ,USERPHONE2  联系电话2 ,OWNER  主叫归属地 ,CONTACTNAME  用户姓名,MANAGER  客户经理 ,USERMANAGERTEL  客户经理联系电话 ,NETTIME  入网时间 ,GREENSIGN  绿通标识 ,FAXNO  传真号码 ,GROUPSERIAL  集团编号 ,GROUPMAINNAME  集团关键人 ,GROUPMAINTEL  集团关键人电话 ,SELLCENTER  营销中心 ,MAINSERVICEATTIUDE  主题服务态度 ,SERIALNO  原单编号,ACCEPTID  受理方式 ,ACCEPTTIME  受理时间,SERVICECITY  受理地市 ,SHEETAREA  工单类别 ,COMPLAINROUTE  热线中心,CONTATCID  联系方式 ,LANGUAGE  客户语种 ,HOPEREPLYTIME  用户希望反馈时间 ,TROUBLEAPPEARANCE  故障描述 ,TERMINATYPE  终端类型 ,BLACKLISTFLAG  是否黑名单 ,ACCEPTERNAME  受理人姓名 ,ACCEPTERTEL  受理人电话 ,DUFFTYPE  工单类型 ,ISUPGRADEAPPEAL  是否升级投诉  ,CALLINCOUNT  是否重复投诉 ,ISFACUSAPPEAL  是否集中投诉工单 ,SPENTERNUM  SP接入号 ,SPSERVICENAME  SP业务名称2 ,SPFACTORY  SP厂家名称2,SPCORPNO  SP企业代码 ,DUTYASCERTAIN  SP责任定性 ,TYPEATTRIBUTE  SP类型属性 ,SPNO  SP代码 ,SPNAME  SP厂家名称1,SPPEACEBREAKER  SP责任人 ,SPCONNECTMODE  SP接入方式 ,SPFORCECUSTOMIZE  SP是否强制制定 ,SPSTARTDATE  SP起始日期 ,SPENDDATE  SP截止日期 ,SPISALL  SP类型,WITHDRAWFEE  期望退费金额,REALWITHDRAWFEE  实际退费金额,COMPENSATETIMES  赔偿倍率 ,COMPENSATETTAL  赔偿小计 ,SERVICECLASSID  SP业务名称1 ,SERVICECONTENT  服务内容,SUMPOWERNUM  工单权值 ,AFFID  工单附件 ,ADDRESS  位置 ,STREETINFO  街道信息 ,BUILDINGINFO  建筑物信息 ,TEMPLATEID  模板ID ,SERVICENAME  业务名称 ,PAYTYPE  赔付类型 ,PAYMETHOD  退赔操作工号 ,ISPAY  是否理赔 ,ISACCEPT  是否操作 ,DUTYORGA  责任部门,OPERATIONPRODUCT  业务产品分类ID ,OPTIONADDBYH1  H1新增存在多条数据的字段集合,PAYMONEY  赔付金额 ,PAYCLASS  赔付方式 ,PAYREASON  理赔原因 ,IMPORT_TIME  导入时间,SERIALINFONO  唯一编号   FROM MOD_COMPLAINTS_ITEM where complaints_id=''{4}''')
", taskid, jobexecman, stepid, phone, itemres.EsheetNo);
            SqlHelper.ExecuteNonQuery(tasksql);

            //插入数据
            string[] datelist = new string[] { "36746", "36747", "36748", "36749", "36741", "36743", "36744", "36745", "36751", "36752", "36753", "36742", "45337", "36750" };
            for (int i = 0; i < datelist.Length; i++)
            {
                string fielddata = "";
                switch (datelist[i])
                {
                    //联系电话
                    case "36746": fielddata = userphone; break;
                    //*投诉单等级
                    case "36747":
                        DateTime musttime = DateTime.Parse(entity.Musthandledate);
                        TimeSpan ts = musttime - DateTime.Now;
                        if (ts.TotalHours > 24)
                        {
                            fielddata = "1";
                        }
                        else
                        {
                            fielddata = "0";
                        }
                        timeoutlength = GetTimeOutLength(fielddata);
                        break;
                    //*紧急程度
                    case "36748":
                        DateTime musttime1 = DateTime.Parse(entity.Musthandledate);
                        TimeSpan ts1 = musttime1 - DateTime.Now;
                        if (ts1.TotalHours > int.Parse(timeout))
                        {
                            fielddata = "2";
                        }
                        else
                        {
                            fielddata = "0";
                        }
                        break;
                    //*投诉类型
                    case "36749":
                        fielddata = "2";
                        break;
                    //*流水号
                    case "36741": break;
                    //城市
                    case "36743": fielddata = userhelp.GetCityId(entity.Usercity).ToString(); break;
                    //*派单时间
                    case "36744": fielddata = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); break;
                    //派单人
                    case "36745": fielddata = username; break;
                    //处理时限
                    case "36750":
                        fielddata = timeoutlength;
                        break;
                    //*投诉内容
                    case "36751": fielddata = entity.Servicecontent; break;
                    //备注
                    case "36752": break;
                    //附件
                    case "36753": break;
                    //*工单主题
                    case "36742": fielddata = phone; break;
                    //是否短信提醒
                    case "45337": fielddata = issms; break;
                }
                string datesql = string.Format(" insert into MOD_TASK_STEPITEMDATA (ID,TASKSTEPID,TMPSTEP_ITEMID,TASK_ID,FIELDDATA) values(SEQ_TASKSTEPDATA.NEXTVAL,{0},{1},'{2}','{3}') ", stepid, datelist[i], taskid, fielddata);
                SqlHelper.ExecuteNonQuery(datesql);
            }

            //更新工单超时时间
            string outtime = GetTimeOut(timeoutlength, userhelp.GetCityId(entity.Usercity).ToString());
            string SqlTime = string.Format("update mod_task set estimatetime=to_date('{0}','yyyy-MM-DD HH24:MI:SS') where task_id='{1}'", outtime, taskid);
            SqlHelper.ExecuteNonQuery(SqlTime);

            //更新原单状态
            string sheetsql = string.Format("UPDATE mod_complaints_item SET billstate='{0}' WHERE sheetid='{1}'", taskid, itemres.EsheetNo);
            SqlHelper.ExecuteNonQuery(sheetsql);

            //更新工单步骤超时时限
            string setuptimeoutsql = string.Format(@"UPDATE mod_task_step SET timelength={0} WHERE task_id='{1}'", timeoutlength, taskid);
            SqlHelper.ExecuteNonQuery(setuptimeoutsql);

            try
            {
                //发送短信
                SendSMS(username, taskid, timeoutlength, itemres.EsheetNo);
            }
            catch (Exception ex)
            {
                Log.Debug("发送短信失败，原单号：" + itemres.EsheetNo);
                Log.Error(ex);
            }

            try
            {
                //发送工单步骤
                ReplyTaskStateToHuaWe(jobexecman, taskid);
            }
            catch (Exception ex)
            {
                Log.Debug("发送工单步骤失败，原单号：" + itemres.EsheetNo);
                Log.Error(ex);
            }
        }

        /// <summary>
        /// 获得当前地市作业计划执行人
        /// </summary>
        /// <param name="cityid"></param>
        /// <returns></returns>
        public string GetJobExecMan(string cityid, ref string phonenumber)
        {
            string giveoutman = "";
            string sql = string.Format(@"SELECT b.giveoutman,c.umobilecode FROM mod_jobplanname a,Mod_Jobitem b,sec_userinfo c
                WHERE a.id=b.planid
                AND b.giveoutman=c.loginname
                AND b.planitemid=52
                AND a.plantime =to_date('{0}','yyyy-mm-dd')
                AND c.city_id={1}", DateTime.Now.ToString("yyyy-MM-01"), cityid);
            DataTable dt = SqlHelper.ExecuteDateSet(sql).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                giveoutman = dt.Rows[0]["giveoutman"].ToString();
                phonenumber = dt.Rows[0]["umobilecode"].ToString();
            }
            return giveoutman;
        }

        /// <summary>
        /// 获得超时时长
        /// </summary>
        /// <param name="tasklevel"></param>
        /// <returns></returns>
        public string GetTimeOutLength(string tasklevel)
        {
            string timeoutlength = "2";
            if (!(string.IsNullOrEmpty(tasklevel)))
            {
                string sql = string.Format(@"SELECT time_value FROM cfg_complaints_time WHERE dept_type=3 AND task_level={0} AND task_step=1", tasklevel);
                timeoutlength = SqlHelper.ExecuteScalar<decimal>(sql).ToString();
            }
            return timeoutlength;
        }

        /// <summary>
        /// 获得工作日时间
        /// </summary>
        /// <param name="userouttime">超时时间</param>
        /// <returns>计算后的超时时间</returns>
        public string GetTimeOut(string userouttime, string cityid)
        {
            int settime = int.Parse(userouttime);
            string outtime = DateTime.Now.AddHours(settime).ToString();//设定的超时时间

            string jobtimesql = string.Format(@"select jobtime from cfg_complaints_auto where cityid={0}", cityid);
            object tempsetjobtime = SqlHelper.ExecuteScalar<string>(jobtimesql).ToString();
            if (tempsetjobtime != null && !string.IsNullOrEmpty(tempsetjobtime.ToString()))
            {
                string setjobtime = tempsetjobtime.ToString();
                if (!string.IsNullOrEmpty(setjobtime) && setjobtime.IndexOf("-") > 0)
                {
                    int starttime = int.Parse(setjobtime.Split('-')[0]);
                    int endtime = int.Parse(setjobtime.Split('-')[1]);
                    DateTime nowtime = DateTime.Now;
                    if (nowtime.Hour >= 0 && nowtime.Hour < starttime)
                    {
                        #region 0~9点，下班时间
                        if (settime >= (endtime - starttime))//如果步骤超时时限大于工作时限
                        {
                            double day = double.Parse((settime / (endtime - starttime)).ToString("#0"));//超时天数
                            double outtimelength = settime - (day * (endtime - starttime));//超时小时
                            DateTime onetime = DateTime.Parse(nowtime.AddDays(day).ToString("yyyy-MM-dd 09:mm:ss")).AddHours(outtimelength);
                            outtime = onetime.ToString();
                        }
                        else
                        {
                            int outtimelength = settime + starttime;//超时时长
                            DateTime onetime = DateTime.Parse(nowtime.ToString("yyyy-MM-dd 00:mm:ss")).AddHours(outtimelength);
                            outtime = onetime.ToString();
                        }
                        #endregion
                    }
                    else if (nowtime.Hour >= starttime && nowtime.Hour < endtime)
                    {
                        #region 9~21点，工作时间范围内的派单
                        if ((nowtime.Hour + settime) < endtime)//当前时间加上超时时间未超出当天工作时间
                        {
                            DateTime twotime = DateTime.Now.AddHours(settime);
                            outtime = twotime.ToString();
                        }
                        else
                        {
                            int usetime = endtime - nowtime.Hour;//离下班的剩余时间
                            if (settime >= (endtime - starttime))
                            {
                                double day = double.Parse((settime / (endtime - starttime)).ToString("#0"));//超时天数
                                double outtimelength = (settime - usetime) - ((day - 1) * (endtime - starttime));//超时小时
                                DateTime twotime = DateTime.Parse(nowtime.AddDays(day).ToString("yyyy-MM-dd 09:mm:ss")).AddHours(outtimelength);
                                outtime = twotime.ToString();
                            }
                            else
                            {
                                double outtimelength = settime - usetime;
                                DateTime twotime = DateTime.Parse(nowtime.AddDays(1).ToString("yyyy-MM-dd 09:mm:ss")).AddHours(outtimelength);
                                outtime = twotime.ToString();
                            }
                        }
                        #endregion
                    }
                    else if (nowtime.Hour >= endtime && nowtime.Hour <= 23)
                    {
                        #region 21~23点，下班时间
                        if (settime >= (endtime - starttime))//如果步骤超时时限大于工作时限
                        {
                            double day = double.Parse((settime / (endtime - starttime)).ToString("#0")) + 1;//超时天数
                            double outtimelength = settime - ((day - 1) * (endtime - starttime));//超时小时
                            DateTime threetime = DateTime.Parse(nowtime.AddDays(day).ToString("yyyy-MM-dd 09:mm:ss")).AddHours(outtimelength);
                            outtime = threetime.ToString();
                        }
                        else
                        {
                            int outtimelength = settime + starttime;
                            DateTime threetime = DateTime.Parse(nowtime.AddDays(1).ToString("yyyy-MM-dd 00:mm:ss")).AddHours(outtimelength);
                            outtime = threetime.ToString();
                        }
                        #endregion
                    }
                }
            }
            return outtime;
        }

        /// <summary>
        /// 发送短消息给下一步执行人
        /// </summary>
        public void SendSMS(string nextman, string taskid, string time, string BillID)
        {
            SendSmsWebService sms = SendSmsWebService.Instance();
            string sql = string.Format(@"select umobilecode,username  from sec_userinfo where loginname='{0}'", nextman);
            DataTable dt = SqlHelper.ExecuteDateSet(sql).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                string phone = dt.Rows[0]["umobilecode"].ToString();//电话号码
                string name = dt.Rows[0]["username"].ToString();//名字
                string smstemplate = ConfigurationSettings.AppSettings["SMSTemplate"].ToString().Trim();//短信模版

                if (string.IsNullOrEmpty(time))
                {
                    time = GetCurrentTime(taskid);//处理时限
                }
                string message = string.Format(smstemplate, name, taskid, time);

                string timesql = "";
                if (!string.IsNullOrEmpty(BillID))
                {
                    timesql = string.Format(@"select MUSTHANDLEDATE from mod_complaints_item where sheetid='{0}'", BillID);
                }
                else if (!string.IsNullOrEmpty(taskid))
                {
                    timesql = string.Format(@"select MUSTHANDLEDATE from mod_complaints_item where billstate='{0}'", taskid);
                }
                object temptime = SqlHelper.ExecuteScalar<string>(timesql);
                if (temptime != null && !string.IsNullOrEmpty(temptime.ToString()))
                {
                    string replytime = "";//最大回复时长
                    DateTime musttime = DateTime.Parse(temptime.ToString());
                    TimeSpan ts = musttime - DateTime.Now;
                    int Days = ts.Days;//天
                    int Hours = ts.Hours;//小时
                    int Minutes = ts.Minutes;//分钟
                    if (Days > 0)
                    {
                        replytime += Days + "天";
                    }
                    if (Hours > 0)
                    {
                        replytime += Hours + "小时";
                    }
                    if (Minutes > 0)
                    {
                        replytime += Minutes + "分钟";
                    }
                    message = message.Replace("请注意查收并处理", string.Format("剩余回复客服时长为{0},回复客服时限为{1}", replytime, musttime.ToString("yyyy-MM-dd HH:mm")));
                }


                sms.DeliverKpi(2103, 1, phone, message, DateTime.Now);//发送短信
            }
        }

        /// <summary>
        /// 根据工单号获得当前工单处理时限
        /// </summary>
        /// <param name="taskid">工单编号</param>
        /// <returns></returns>
        private string GetCurrentTime(string taskid)
        {
            string time = "2";
            string sql = string.Format(@"select timelength from mod_task_step where task_id = '{0}' order by exectime desc", taskid);
            object temptime = SqlHelper.ExecuteScalar<decimal>(sql);
            if (temptime != null)
            {
                time = temptime.ToString();
            }
            return time;
        }

        private void ReplyTaskStateToHuaWe(string execman, string taskid)
        {
            string execmanname = "";//当前执行人姓名
            string deptname = "";//当前执行部门名称

            string deptsql = string.Format(@"SELECT a.username,b.deptname,b.depttype FROM sec_userinfo a,sec_dept b WHERE a.deptid=b.id AND a.loginname='{0}'", execman);
            DataSet deptds = SqlHelper.ExecuteDateSet(deptsql);
            if (deptds != null && deptds.Tables.Count > 0)
            {
                if (deptds.Tables[0] != null && deptds.Tables[0].Rows.Count > 0)
                {
                    execmanname = deptds.Tables[0].Rows[0]["username"].ToString();
                    deptname = deptds.Tables[0].Rows[0]["deptname"].ToString();
                    string type = deptds.Tables[0].Rows[0]["depttype"].ToString();
                    switch (type)
                    {
                        case "0": deptname = "[默认]" + deptname; break;
                        case "1": deptname = "[网建]" + deptname; break;
                        case "2": deptname = "[运维]" + deptname; break;
                        case "3": deptname = "[网优]" + deptname; break;
                    }
                }
            }

            string message = string.Format("步骤1、步骤名称：派单。处理部门：{0}。处理人：{1}。处理时间：{2}。", deptname, execmanname, DateTime.Now);
            if (!string.IsNullOrEmpty(message) && !string.IsNullOrEmpty(deptname))
            {
                string sqlSel = string.Format(@"select sqlvalue from mod_task where task_id='{0}'", taskid);
                object obcIDsql = SqlHelper.ExecuteScalar<string>(sqlSel);
                DataTable datID = null;
                if (obcIDsql != null && obcIDsql.ToString() != "")
                {
                    DataSet ds = SqlHelper.ExecuteDateSet(obcIDsql.ToString());
                    if (ds != null && ds.Tables.Count > 0)
                    {
                        datID = ds.Tables[0];
                    }
                }
                string serialNo = "";
                string serialNo1 = "";
                if (datID != null && datID.Rows.Count > 0)
                {
                    serialNo = datID.Rows[0]["原单编号"].ToString();//
                    serialNo1 = datID.Rows[0]["唯一编号"].ToString();//

                    SendReplayToService send = new SendReplayToService();
                    string remark = "";
                    //serialNo = "2011033000000040";
                    if (!send.CallService(3, serialNo1, message, execman, "", ref remark))
                    {
                        Log.Debug("回复客服失败，返回错误信息:" + remark);
                    }
                    else
                    {
                        Log.Debug("回复客服成功，回复信息:" + message);
                    }
                }
                else
                {
                    Log.Debug("未查到原单数据，回复客服失败！");
                }
            }
        }
        
        // 20120413 add by zhangfy
        public void StopTaskSetpState(NewSheetResponse itemres)
        {
            try
            {
                string tasksql = string.Format(@"select billstate from mod_complaints_item where import_time > to_date('2012-04-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and serialno='{0}' and complaints_id <> '{1}' order by import_time desc", itemres.SerialNo, itemres.EsheetNo);
                object taskid = SqlHelper.ExecuteScalar<string>(tasksql);
                //如果有工单信息
                if (taskid != null && !string.IsNullOrEmpty(taskid.ToString()) && taskid.ToString().ToUpper().IndexOf("XT") >= 0)
                {
                    string updatesql = string.Format(@"update mod_task_step set taskstepstate=2 where id in (select id from mod_task_step where task_id='{0}' and taskstepstate=1)", taskid);
                    SqlHelper.ExecuteNonQuery(updatesql);
                }
            }
            catch (Exception ex)
            {
                Log.Debug("修改工单步骤失败，客服原单号：" + itemres.SerialNo);
                Log.Error(ex);
            }
        }
    }
}
